﻿CREATE procedure sh_ArchiveRow
	@LoanID int,-- = 191347,
	@Object varchar(128),-- = 'v_FaxToDealer',
	@KeyValue varchar(128) = '',
	@KeyName varchar(128) = '',
	@KeyValue1 varchar(128) = '',
	@KeyName1 varchar(128) = ''
AS

SET NOCOUNT ON

DECLARE @ColName varchar(128),@SQL varchar(8000),@SQL1 varchar(8000),@S nvarchar(4000),
				@UserName varchar(128),@MenuID int,@HistID bigint,@IndexAND varchar(128),@ColOrder int
SET @UserName = dbo.UserName()

IF @UserName IS NULL RETURN

IF @KeyValue = '' SET @KeyValue = '0'
ELSE SET @IndexAND = ' AND ['+@KeyName+']='+@KeyValue

IF @KeyValue1 = '' SET @KeyValue1 = '0'
ELSE SET @IndexAND = @IndexAND + ' AND ['+@KeyName1+']='+@KeyValue1

DECLARE @CurCols CURSOR

EXEC sec__GetColumnList @Object,@CurCols OUTPUT

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION sh_ArchiveRow

-- Prepare SQL statement
	SELECT @MenuID = MenuID FROM t_Loan WHERE ID = @LoanID
	
	INSERT INTO thist_Root (ObjectName,PK_1,PK_2)	VALUES (@Object,@LoanID,@MenuID)
	
	SET @HistID = IDENT_CURRENT('thist_Root')
------------------------

FETCH NEXT FROM @CurCols INTO @ColOrder,@ColName

WHILE @@FETCH_STATUS = 0
BEGIN

	SET @S = '
			INSERT INTO thist_Child(HID,Key_1,Key_2,CN,CV)
			SELECT @HistID,'+@KeyValue+','+@KeyValue1+',@ColName,['+@ColName+']
			FROM ['+@Object+'] WHERE LoanID = @LoanID'+ISNULL(@IndexAND,'')

	--PRINT @S
	
	EXEC sp_executesql @S,N'@HistID int,@ColName varchar(128),@LoanID int',@HistID,@ColName,@LoanID
	
	FETCH NEXT FROM @CurCols INTO @ColOrder,@ColName

END

if not exists (select * from dbo.sysobjects where id = object_id(N'vhist_'+@Object) and OBJECTPROPERTY(id, N'IsView') = 1)
	EXEC sh_Archive_MakeView @Object

COMMIT TRAN sh_ArchiveRow

CLOSE @CurCols
DEALLOCATE @CurCols




